/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package adapdateurs;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import moyensDeTransport.Bateau;
import webservices.Distance;
import webservices.Ville;

/**
 *
 * @author BENAMMAR Riyadh
 */
public class BateauSQL implements Adapdateur{

    Statement state;
    @Override
    public String traduire(String requete) {
        String [] champs=requete.split(":");
        String aRetourner="SELECT ID_VOYAGE,VILLE_DEPART,VILLE_ARRIVEE,HEURE_DEPART,HEURE_ARRIVEE,DATE_ALLE,V.BATEAU.NOM,PRIX FROM VOYAGE V"
                + " WHERE VILLE_DEPART=\'"+champs[1]+"\' AND VILLE_ARRIVEE=\'"+champs[3]+"\' "
                + "AND DATE_ALLE>="+String.valueOf(Integer.parseInt(champs[5])-Integer.parseInt(champs[7]))+""
                + " AND DATE_ALLE<="+String.valueOf(Integer.parseInt(champs[5])+Integer.parseInt(champs[7]))+"";
        return aRetourner;
    }

    @Override
    public List executerRequete(String requete) {
        List<Bateau> resultats=new ArrayList<>();
        try {
            connection();
            System.out.println("requete: "+requete);
            
            ResultSet resultSet=state.executeQuery(requete);
            
            ResultSetMetaData metaData=resultSet.getMetaData();
            for (int column = 1; column < metaData.getColumnCount(); column++) {
                System.out.println("c: "+column+": "+metaData.getColumnLabel(column));
            }
            
            int cpt=0;
            while(resultSet.next()){
                Bateau bateau=new Bateau();
                String depart=resultSet.getString("VILLE_DEPART");
                bateau.setAdresseDepart(depart);
                String arrivee=resultSet.getString("VILLE_ARRIVEE");
                bateau.setAdresseArrivee(arrivee);
                Calendar c=Calendar.getInstance();
                c.set(Calendar.DAY_OF_YEAR, resultSet.getInt("DATE_ALLE"));
                c.set(Calendar.HOUR_OF_DAY, resultSet.getInt("HEURE_DEPART"));
                c.set(Calendar.MINUTE, resultSet.getInt("HEURE_ARRIVEE"));
                bateau.setDateDebut(c.getTime());
                bateau.setPrix(resultSet.getInt("PRIX"));
                bateau.setReseauDeTransport(resultSet.getString(7));
                // utilisation du web service pour récuperer les coordonnées de chaque ville
                Ville v1=new Ville(depart);
                Ville v2=new Ville(arrivee);
                
                Distance distance=new Distance(v1, v2);
                double dist=distance.calculDistance();
                
                Double d=new Double(bateau.estimation(dist));
                int durree=d.intValue();
                bateau.setDuree(durree);
                c.set(Calendar.HOUR_OF_DAY, c.get(Calendar.HOUR_OF_DAY)+durree);
                bateau.setDateFin(c.getTime());
                cpt++;
                resultats.add(bateau);
                c.set(Calendar.DAY_OF_YEAR, Integer.parseInt(resultSet.getString("DATE_ALLE")));
                System.out.println("resultat "+cpt+": jour "+c.toString()+" cout "+resultSet.getString("PRIX"));
            }
            
            
        } catch (SQLException ex) {
            Logger.getLogger(VolsSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return resultats;
    }

    private void connection() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection connection = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:XE", "SYSTEM",
                        "azerty00");
                state = connection.createStatement();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(BateauSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(BateauSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
}
